# models.py
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import update
from datetime import datetime,timedelta
from werkzeug.security import generate_password_hash, check_password_hash
from sqlalchemy.orm import validates


db = SQLAlchemy()
class User(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    password_hash = db.Column(db.String(128), nullable=False)

    def set_password(self, password):
        self.password_hash = generate_password_hash(password)

    def check_password(self, password):
        return check_password_hash(self.password_hash, password)

    @validates('username')
    def validate_username(self, key, username):
        if not username:
            raise ValueError('Username cannot be empty')
        if len(username) < 3:
            raise ValueError('Username must be at least 3 characters long')
        return username

    @validates('password_hash')
    def validate_password(self, key, password):
        if not password:
            raise ValueError('Password cannot be empty')
        if len(password) < 8:
            raise ValueError('Password must be at least 8 characters long')
        return password

class Session(db.Model):
    __tablename__ = 'sessions'
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
    session_data = db.Column(db.Text, nullable=False)
    expiration_date = db.Column(db.DateTime, nullable=False)
    user = db.relationship('User', backref=db.backref('sessions', lazy=True))

    def __init__(self, user_id, session_data, expiration_date):
        self.user_id = user_id
        self.session_data = session_data
        self.expiration_date = expiration_date

class AccountStatement(db.Model):
    __tablename__ = 'tAcccount_Statement'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    sname = db.Column(db.String(20), nullable=False)
    sdate = db.Column(db.Date, nullable=False)
    sInvestmentAmount = db.Column(db.Float, nullable=False)
    sNetAssetValue = db.Column(db.Float, nullable=False)
    sAction = db.Column(db.Integer, nullable=False)
    sInvestmentShare = db.Column(db.Float, nullable=False)
    dtinsert = db.Column(db.DateTime, default=datetime.now)

class AccountInfo(db.Model):
    __tablename__ = 'tAccount_Info'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    sname = db.Column(db.String(20), nullable=False)
    sInvestmentAmount = db.Column(db.Float, nullable=False)
    sTotalShare = db.Column(db.Float, nullable=False)
    dtinsert = db.Column(db.DateTime, default=datetime.now)

class FundsInfo(db.Model):
    __tablename__ = 'tFunds_Info'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    sNetAssetValue = db.Column(db.Float, nullable=False)
    sInvestmentAmount = db.Column(db.Float, nullable=False)
    sTotalShare = db.Column(db.Float, nullable=False)
    dtinsert = db.Column(db.DateTime, default=datetime.now)

class DividendsDetail(db.Model):
    __tablename__ = 'tDividends_Detail'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    sname = db.Column(db.String(20), nullable=False)
    sShare = db.Column(db.Float, nullable=False)
    sDividends = db.Column(db.Float, nullable=False)
    sdate = db.Column(db.Date, nullable=False)
    dtinsert = db.Column(db.DateTime, default=datetime.now)

class NetAssetValue(db.Model):
    __tablename__ = 'tNetAsset_Value'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    sdate = db.Column(db.Date, nullable=False)
    sNetAssetValue = db.Column(db.Float, nullable=False)
    sTotalShare = db.Column(db.Float, nullable=False)
    dtinsert = db.Column(db.DateTime, default=datetime.now)

class Dividends(db.Model):
    __tablename__ = 'tDividends'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    sdate = db.Column(db.Date, nullable=False)
    sTotalShare = db.Column(db.Float, nullable=False)
    sDividendRatio = db.Column(db.Float, nullable=False)
    sDividends = db.Column(db.Float, nullable=False)
    dtinsert = db.Column(db.DateTime, default=datetime.now)

# 版本管理相关函数
def get_version_info():
    """获取当前系统版本信息"""
    return VersionInfo.query.order_by(VersionInfo.updated_at.desc()).first()

def set_version_info(version, description=None, schema_version=None, api_version=None):
    """更新系统版本信息"""
    version_info = VersionInfo.query.filter_by(version=version).first()
    if version_info:
        version_info.description = description
        version_info.schema_version = schema_version
        version_info.api_version = api_version
        version_info.updated_at = datetime.utcnow()
    else:
        version_info = VersionInfo(
            version=version,
            description=description,
            schema_version=schema_version,
            api_version=api_version
        )
        db.session.add(version_info)
    db.session.commit()

#时间格式检查函数
def to_datetime(date_input):
    """
    Convert a date string to a datetime object.
    
    :param date_input: A date string in 'YYYY-MM-DD' format or a datetime object.
    :return: A datetime object.
    """
    if date_input=='':
        return date_input
    elif isinstance(date_input, str):
        # 如果输入是字符串，则将其转换为 datetime 对象
        return datetime.strptime(date_input, '%Y-%m-%d')
    elif isinstance(date_input, datetime):
        # 如果输入已经是一个 datetime 对象，则直接返回
        return date_input
    else:
        raise TypeError("date_input must be a string or datetime object")

# 数据库初始化函数
def init_db(app):
    with app.app_context():
        db.init_app(app)
        # 创建所有表
        db.create_all()
        

#后台添加默认用户       
def add_user_background(username, password):
    existing_user = User.query.filter_by(username=username).first()
    if not existing_user:
        new_user = User(username=username, password_hash=generate_password_hash(password))
        db.session.add(new_user)
        db.session.commit() 

# 数据库查询和更新操作
def get_user_by_username(username):
    return User.query.filter_by(username=username).with_entities(User.id, User.username).first()

def funds_detail():
    funds_info = FundsInfo.query.first()
    if funds_info:
        sNetAssetValue=funds_info.sNetAssetValue
        InvestmentAmount=funds_info.sInvestmentAmount
        TotalShare=funds_info.sTotalShare
        TotalAssets=sNetAssetValue*TotalShare
        Earnings=TotalAssets-InvestmentAmount
        if InvestmentAmount!=0:
            Yield=(Earnings/InvestmentAmount)*100
        else:
            Yield=0
        updatetime = funds_info.dtinsert
    else:
        sNetAssetValue=1
        InvestmentAmount=0
        TotalShare=0
        TotalAssets=0
        Earnings=0
        Yield=0
        updatetime=0
        new_FundsInfo=FundsInfo(sNetAssetValue=sNetAssetValue,sTotalShare=TotalShare,sInvestmentAmount=InvestmentAmount)
        db.session.add(new_FundsInfo)
        db.session.commit()
    result_list=[sNetAssetValue,InvestmentAmount,TotalShare,TotalAssets,Earnings,Yield,updatetime]
    result_dic={'sNetAssetValue':float("%.3f" %sNetAssetValue),
            'InvestmentAmount':float("%.2f" %InvestmentAmount),
            'TotalShare':float("%.2f" %TotalShare),
            'TotalAssets':float("%.2f" %TotalAssets),
            'Earnings':float("%.2f" %Earnings),
            'Yield':float("%.2f" %Yield)
        }
    if updatetime==0:
        result_dic['updatetime']=updatetime
    else:
        result_dic['updatetime']=updatetime.strftime('%Y-%m-%d %H:%M:%S')
    return [result_list,result_dic]
    #[u'净值',u'总投资金额',u'总份额',u'总资产',u'盈利',u'盈利率',u'更新时间']

def update_funds(TotalAssets,date):
    date=to_datetime(date)
    if TotalAssets==0:
        return '2'
    else:
        funds_info = FundsInfo.query.first()
        sNetAssetValue=funds_info.sNetAssetValue
        TotalShare=funds_info.sTotalShare
        new_sNetAssetValue=TotalAssets/TotalShare
        if new_sNetAssetValue==sNetAssetValue:
            return '2'
        else:
            #更新总账户表
            funds_info.sNetAssetValue=new_sNetAssetValue
            funds_info.dtinsert=date
            db.session.commit()
            #更新净值表
            new_NetAsset_Value=NetAssetValue(sNetAssetValue=new_sNetAssetValue,sTotalShare=TotalShare,sdate=date)
            db.session.add(new_NetAsset_Value)
            db.session.commit()
            #更新个人账户更新时间
            stmt = (
                update(AccountInfo).
                values(dtinsert=datetime.now())
            )
            db.session.execute(stmt)
            db.session.commit()
            return '1'


def account_detail(name='',result_type='dic'):
    sNetAssetValue=funds_detail()[0][0]
    accountinfo=AccountInfo.query.filter(AccountInfo.sTotalShare > 0).all()
    account_dic={}
    accountinfo_dicts=[{
        'sname':entity.sname,
        'sInvestmentAmount':entity.sInvestmentAmount,
        'sTotalShare':entity.sTotalShare,
        'dtinsert':entity.dtinsert
    } for entity in accountinfo]
    if len(accountinfo_dicts)>0:
        for d in accountinfo_dicts:
            sname=d['sname']
            InvestmentAmount=d['sInvestmentAmount']
            TotalShare=d['sTotalShare']
            TotalAssets=TotalShare*sNetAssetValue
            Earnings=TotalAssets-InvestmentAmount
            if InvestmentAmount!=0:
                Yield=(Earnings/InvestmentAmount)*100
            else:
                Yield=0
            inserttime=d['dtinsert']
            #account_dic[sname]=[InvestmentAmount,TotalShare,TotalAssets,Earnings,Yield,inserttime]
            account_dic[sname]={
                                'sname':sname,
                                'InvestmentAmount':float("%.2f" %InvestmentAmount),
                                'TotalShare':float("%.2f" %TotalShare),
                                'TotalAssets':float("%.2f" %TotalAssets),
                                'Earnings':float("%.2f" %Earnings),
                                'Yield':float("%.2f" %Yield),
                                'inserttime':inserttime.strftime('%Y-%m-%d %H:%M:%S')
                                }
    if result_type=='dic':
        if name not in account_dic and name !='':
            return []
            '''
            account_dic[name]={
                                'InvestmentAmount':0,
                                'TotalShare':0,
                                'TotalAssets':0,
                                'Earnings':0,
                                'Yield':0,
                                'inserttime':0
                                }
            #新增投资人，先在个人账户表插入投资人信息
            new_account=AccountInfo(sname=name,sInvestmentAmount=0,sTotalShare=0)
            db.session.add(new_account)
            db.session.commit()
            '''    
        elif name !='':
            return {name:account_dic[name]}
        else:
            return account_dic
        #[u'姓名',u'总投资金额',u'总份额',u'总资产',u'盈利',u'盈利率',u'更新时间']
    else:
        if name not in account_dic and name !='':
            return []
        elif name !='':
            return [account_dic[name]]
        else:
            account_list=[]
            for key in account_dic:
                account_list.append(account_dic[key])
            return account_list

def buy_funds(date,name,money,action,oldTotalAssets='',ifall='yes'):
    date=to_datetime(date)
    fundsinfo=funds_detail()[0]
    if oldTotalAssets !='' and oldTotalAssets != fundsinfo[3]:
        update_funds(oldTotalAssets,date)
    accountinfos=account_detail(name)
    if accountinfos==[]:
        new_account=AccountInfo(sname=name,sInvestmentAmount=0,sTotalShare=0)
        db.session.add(new_account)
        db.session.commit()
    accountinfo=account_detail(name)[name]
    sNetAssetValue=fundsinfo[0]
    #买入用金额算份额，赎回用份额算金额
    if action==0:
        if ifall=='yes':
            sInvestmentShare=0-accountinfo['TotalShare']
            money=sInvestmentShare*sNetAssetValue
        else:
            sInvestmentShare=0-money/sNetAssetValue
            money=0-money
        if accountinfo['InvestmentAmount']+money>0:
            fundsinfo_money=money
        else:
            fundsinfo_money=0-accountinfo['InvestmentAmount']
    else:
        sInvestmentShare=money/sNetAssetValue
        fundsinfo_money=money
    #流水表入库
    new_Acccount_Statement=AccountStatement(sname=name,sdate=date,sInvestmentAmount=money,sNetAssetValue=sNetAssetValue,sAction=action,sInvestmentShare=sInvestmentShare)
    db.session.add(new_Acccount_Statement)
    db.session.commit()

    #更新个人账户
    column='sname,sInvestmentAmount,sTotalShare'
    if action!=2:
        sInvestmentAmount=accountinfo['InvestmentAmount']+money
    else:
        sInvestmentAmount=accountinfo['InvestmentAmount']
    if sInvestmentAmount <0:
        sInvestmentAmount=0
    #sTotalShare=float("%.4f" %(accountifo[1]+sInvestmentShare))
    sTotalShare=accountinfo['TotalShare']+sInvestmentShare
    stmt = (
        update(AccountInfo).
        where(AccountInfo.sname == name).  # 替换为实际的条件
        values(sInvestmentAmount=sInvestmentAmount, sTotalShare=sTotalShare,dtinsert=date)
    )
    db.session.execute(stmt)
    db.session.commit()

    #更新汇总表
    if action!=2:
        sInvestmentAmount=fundsinfo[1]+fundsinfo_money
    else:
        sInvestmentAmount=fundsinfo[1]
    sTotalShare=fundsinfo[2]+sInvestmentShare
    stmt = (
        update(FundsInfo).
        values(sInvestmentAmount=sInvestmentAmount, sTotalShare=sTotalShare,dtinsert=datetime.now())
    )
    db.session.execute(stmt)
    db.session.commit()
    return 'Success'

def Dividend(DividendRatio,date):
    date=to_datetime(date)
    #[sNetAssetValue,InvestmentAmount,TotalShare,TotalAssets,Earnings,Yield,updatetime]
    #[u'净值',u'总投资金额',u'总份额',u'总资产',u'盈利',u'盈利率',u'更新时间']
    TotalShare=funds_detail()[0][2]
    TotalAssets=funds_detail()[0][3]
    sDividends='%.4f' %(TotalShare*DividendRatio)
    new_Dividends=Dividends(sdate=date,sTotalShare=TotalShare,sDividendRatio=DividendRatio,sDividends=sDividends)
    db.session.add(new_Dividends)
    db.session.commit()
    accountinfo=account_detail()
    new_TotalAssets=TotalAssets-TotalShare*DividendRatio
    update_funds(new_TotalAssets,date)
    for name in accountinfo:
        sShare=accountinfo[name]['TotalShare']
        if sShare!=0:
            #sDividends="%.4f" %(sShare*DividendRatio)
            sDividends=sShare*DividendRatio
            new_Dividends_Detail=DividendsDetail(sname=name,sShare=sShare,sDividends=sDividends,sdate=date)
            db.session.add(new_Dividends_Detail)
            db.session.commit()
    return 'Success'

def Dividends_detail(date=''):
    date=to_datetime(date)
    if date!='':
        dividends_result=Dividends.query.filter_by(sdate=date).with_entities(Dividends.sTotalShare,Dividends.sDividendRatio,Dividends.sDividends,Dividends.sdate).order_by(Dividends.sdate.desc()).all()
    else:
        dividends_result=Dividends.query.with_entities(Dividends.sTotalShare,Dividends.sDividendRatio,Dividends.sDividends,Dividends.sdate).order_by(Dividends.sdate.desc()).all()
    dividends_dicts=[{       
        'sTotalShare':float("%.2f" %entity.sTotalShare),
        'sDividendRatio':float("%.2f" %entity.sDividendRatio),
        'sDividends':float("%.2f" %entity.sDividends),
        'sdate':entity.sdate.strftime('%Y-%m-%d'),
    } for entity in dividends_result]
    return dividends_dicts
    #[u'总分红份额',u'每份红利金额',u'分��金额',u'分红日期']

def Dividends_person_detail(name='',date=''):
    date=to_datetime(date)
    if date!='' and name!='':
        Dividends_Detail=DividendsDetail.query.filter_by(sdate=date,sname=name).with_entities(DividendsDetail.sname,DividendsDetail.sShare,DividendsDetail.sDividends,DividendsDetail.sdate).order_by(DividendsDetail.sdate.desc()).all()
    elif date!='':
        Dividends_Detail=DividendsDetail.query.filter_by(sdate=date).with_entities(DividendsDetail.sname,DividendsDetail.sShare,DividendsDetail.sDividends,DividendsDetail.sdate).order_by(DividendsDetail.sdate.desc()).all()
    elif name!='':
        Dividends_Detail=DividendsDetail.query.filter_by(sname=name).with_entities(DividendsDetail.sname,DividendsDetail.sShare,DividendsDetail.sDividends,DividendsDetail.sdate).order_by(DividendsDetail.sdate.desc()).all()
    else:
        Dividends_Detail=DividendsDetail.query.with_entities(DividendsDetail.sname,DividendsDetail.sShare,DividendsDetail.sDividends,DividendsDetail.sdate).order_by(DividendsDetail.sdate.desc()).all()

    Dividends_Detail_dicts=[{
        'sname':entity.sname,        
        'sShare':float("%.2f" %entity.sShare),
        'sDividends':float("%.2f" %entity.sDividends),
        'sdate':entity.sdate.strftime('%Y-%m-%d'),
    } for entity in Dividends_Detail]
    return Dividends_Detail_dicts
    #[u'姓名',u'分红份额',u'分红金额',u'分红日期']

def Acccount_Statement(name='',date=''):
    date=to_datetime(date)
    if date!='' and name!='':
        Acccount_Statement_result=AccountStatement.query.filter_by(sdate=date,sname=name).with_entities(AccountStatement.sname,AccountStatement.sdate,AccountStatement.sInvestmentAmount,AccountStatement.sNetAssetValue,AccountStatement.sAction,AccountStatement.sInvestmentShare).order_by(AccountStatement.sdate.desc()).all()
    elif date!='':
        Acccount_Statement_result=AccountStatement.query.filter_by(sdate=date).with_entities(AccountStatement.sname,AccountStatement.sdate,AccountStatement.sInvestmentAmount,AccountStatement.sNetAssetValue,AccountStatement.sAction,AccountStatement.sInvestmentShare).order_by(AccountStatement.sdate.desc()).all()
    elif name!='':
        Acccount_Statement_result=AccountStatement.query.filter_by(sname=name).with_entities(AccountStatement.sname,AccountStatement.sdate,AccountStatement.sInvestmentAmount,AccountStatement.sNetAssetValue,AccountStatement.sAction,AccountStatement.sInvestmentShare).order_by(AccountStatement.sdate.desc()).all()
    else:
        Acccount_Statement_result=AccountStatement.query.with_entities(AccountStatement.sname,AccountStatement.sdate,AccountStatement.sInvestmentAmount,AccountStatement.sNetAssetValue,AccountStatement.sAction,AccountStatement.sInvestmentShare).order_by(AccountStatement.sdate.desc()).all()
    Acccount_Statement_dicts=[{
        'sname':entity.sname,
        'sdate':entity.sdate.strftime('%Y-%m-%d'),
        'sInvestmentAmount':float("%.2f" %entity.sInvestmentAmount),
        'sNetAssetValue':float("%.2f" %entity.sNetAssetValue),
        'sAction':entity.sAction,
        'sInvestmentShare':float("%.2f" %entity.sInvestmentShare)
    } for entity in Acccount_Statement_result]
    
    return Acccount_Statement_dicts
    #[u'姓名',u'投资日期',u'投资金额',u'投资时净值',u'操作',u'投资份额']

def get_NetAssetValue(time_param):
    """
    获取净值信息
    :param time_param: 'all'/'month'/'week'/'this-month'/'year' 或者日期字符串 'YYYY-MM-DD'
    :return: 净值信息列表
    """
    if time_param == 'all':
        result = NetAssetValue.query.order_by(NetAssetValue.sdate.desc()).all()
    elif time_param == 'month':
        # 获取最近一个月的数据
        one_month_ago = datetime.now() - timedelta(days=30)
        result = NetAssetValue.query.filter(
            NetAssetValue.sdate >= one_month_ago.date()
        ).order_by(NetAssetValue.sdate.desc()).all()
    elif time_param == 'week':
        # 获取最近一周的数据
        one_week_ago = datetime.now() - timedelta(days=7)
        result = NetAssetValue.query.filter(
            NetAssetValue.sdate >= one_week_ago.date()
        ).order_by(NetAssetValue.sdate.desc()).all()
    elif time_param == 'this-month':
        # 获取本月数据
        today = datetime.now()
        first_day = today.replace(day=1)
        result = NetAssetValue.query.filter(
            NetAssetValue.sdate >= first_day.date()
        ).order_by(NetAssetValue.sdate.desc()).all()
    elif time_param == 'year':
        # 获取本年数据
        today = datetime.now()
        first_day = today.replace(month=1, day=1)
        result = NetAssetValue.query.filter(
            NetAssetValue.sdate >= first_day.date()
        ).order_by(NetAssetValue.sdate.desc()).all()
    else:
        try:
            # 尝试解析具体日期
            query_date = to_datetime(time_param).date()
            result = NetAssetValue.query.filter(
                NetAssetValue.sdate >= query_date
            ).order_by(NetAssetValue.sdate.desc()).all()
        except (ValueError, TypeError):
            # 如果日期解析失败，返回所有数据
            current_app.logger.warning(f'Invalid date format: {time_param}, returning all data')
            result = NetAssetValue.query.order_by(NetAssetValue.sdate.desc()).all()

    NetAssetValue_dics = [{      
        'sNetAssetValue': float("%.3f" % entity.sNetAssetValue),
        'sdate': entity.sdate.strftime('%Y-%m-%d'),
    } for entity in result]
    
    return NetAssetValue_dics

if __name__ == '__main__':
    print()